IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetMenuSystem')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetMenuSystem
GO

/****** Object:  StoredProcedure [dbo].[USP_GetSystemListByUserPrivilege]    Script Date: 10/22/2010 13:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 21/10/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetMenuSystem]
	-- Add the parameters for the stored procedure here
	@main_system_code int
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	select distinct main.main_system_code,
		main.main_system_name,
		menu.menu_system_code,
		menu.menu_system_name,
		main.main_system_type
	from stp_sub_menu sub_menu
	inner join stp_menu_system menu on menu.menu_system_code = sub_menu.menu_system_code 
	inner join stp_main_system main on main.main_system_code = menu.main_system_code
	where menu.menu_system_status = 1
			and @main_system_code in (0, main.main_system_code)
	order by main.main_system_code
	
END



GO
